﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PromotionApp.Model;
using System.Text;
using PromotionApp.Utility;
using PromotionApp.DAL;
using System.Data;

namespace PromotionApp.BLL
{
    public class InternalUserBLL
    {
        #region Private Variable

        private static InternalUserBLL _InternalUserBLL = null;

        #endregion 

        #region Constructor

        private InternalUserBLL()
        {            
        }

        #endregion

        #region Private Method

        private string GetUserNameCondition(ref bool blnConditionFlag, string strUserName)
        {
            string strCondition = string.Empty;

            if (strUserName.Length > 0)
                strCondition = string.Format(" {0} a.[UserName] like '%{1}%'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strUserName);

            return strCondition;
        }

        private string GetUserTypeCodeCondition(ref bool blnConditionFlag, string strUserTypeCode)
        {
            string strCondition = string.Empty;

            if (strUserTypeCode.Length > 0)
                strCondition = string.Format(" {0} a.[UserTypeCode] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strUserTypeCode);

            return strCondition;
        }

        private string GetStatusCodeCondition(ref bool blnConditionFlag, string strStatusCode)
        {
            string strCondition = string.Empty;

            if (strStatusCode.Length > 0)
                strCondition = string.Format(" {0} a.[StatusCode] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strStatusCode);

            return strCondition;
        }

        private string GetSearchInternalUserCTESql(string strUserName, string strUserTypeCode, string strStatusCode)
        {
            StringBuilder sb = new StringBuilder();
            bool blnConditionFlag = false;

            sb.Append(" With temp as");
            sb.Append(" (");
            sb.Append(" SELECT row_number() over(order by a.[UserId] asc) as [No]");
		    sb.Append(" , a.[UserId]");
		    sb.Append(" ,a.[UserName]");
		    sb.Append(" ,a.[UserTypeCode]");
            sb.Append(" ,isnull(b.[UserTypeName], '') as [UserTypeName]");
		    sb.Append(" ,a.[StatusCode]");
            sb.Append(" ,isnull(c.[StatusName], '') as [StatusName]");
		    sb.Append(" ,a.[CreatedBy]");
		    sb.Append(" ,isnull(d.[UserName], '')  as [CreatedByName]");
		    sb.Append(" ,a.[CreatedDate]");
		    sb.Append(" ,a.[UpdatedBy]");
		    sb.Append(" ,isnull(e.[UserName], '') as [UpdatedByName]");
		    sb.Append(" ,a.[UpdatedDate]");
	        sb.Append(" FROM [dbo].[MsInternalUser] as a");
		    sb.Append(" left join dbo.[LkUserType] as b on a.[UserTypeCode] = b.[UserTypeCode]");
		    sb.Append(" left join dbo.[LkStatus] as c on a.[StatusCode] = c.[StatusCode]");
		    sb.Append(" left join dbo.[MsInternalUser] as d on a.[CreatedBy] = d.[UserId]");
		    sb.Append(" left join dbo.[MsInternalUser] as e on a.[UpdatedBy] = e.[UserId]");
            sb.Append(GetUserNameCondition(ref blnConditionFlag, strUserName));
            sb.Append(GetUserTypeCodeCondition(ref blnConditionFlag, strUserTypeCode));
            sb.Append(GetStatusCodeCondition(ref blnConditionFlag, strStatusCode));
            sb.Append(" )");

            return sb.ToString();
        }

        private string GetSearchInternalUserSql(string strCTEQuery, int pageSize, int pageNumber)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select [No], [UserId], [UserName], [UserTypeCode], [UserTypeName], [StatusCode], [StatusName], [CreatedBy], [CreatedByName], [CreatedDate], [UpdatedBy], [UpdatedByName], [UpdatedDate]");
            sb.Append(" from temp");
            sb.AppendFormat(" where [No] between {0} and {1}", DynamicQueryHelper.GetStartNo(pageSize, pageNumber), DynamicQueryHelper.GetEndNo(pageSize, pageNumber));

            return sb.ToString();
        }

        private string GetSearchInternalUserCountSql(string strCTEQuery)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select count(*) as [RowCount]");
            sb.Append(" from temp");

            return sb.ToString();
        }

        #endregion

        #region Public Method

        public List<InternalUser> SearchInternalUser(InternalUserPagingArgs e)
        {
            List<InternalUser> list = new List<InternalUser>();
            string strCTESql = GetSearchInternalUserCTESql(e.UserName, e.UserTypeCode, e.StatusCode);
            string strSearchSql = GetSearchInternalUserSql(strCTESql, e.PageSize, e.PageNumber);
            string strCountQuery = GetSearchInternalUserCountSql(strCTESql);

            e.Total = InternalUserDAL.GetInstance().GetTotalRow(strCountQuery);
            DynamicQueryHelper.GetMaxPageNumber(strCountQuery, e);
            DataSet ds = InternalUserDAL.GetInstance().GetDataSetFromSql(strSearchSql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    InternalUser o = new InternalUser();

                    o.No = Convert.ToInt32(r["No"]);
                    o.UserId = Convert.ToInt32(r["UserId"]);
                    o.UserName = r["UserName"].ToString().Trim();
                    o.UserTypeCode = r["UserTypeCode"].ToString().Trim();
                    o.UserTypeName = r["UserTypeName"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.StatusName = r["StatusName"].ToString().Trim();
                    o.CreatedById = Convert.ToInt32(r["CreatedBy"]);
                    o.CreatedByName = r["CreatedByName"].ToString().Trim();
                    o.CreatedByDate = Convert.ToDateTime(r["CreatedDate"]);
                    o.UpdatedById = Convert.ToInt32(r["UpdatedBy"]);
                    o.UpdatedByName = r["UpdatedByName"].ToString().Trim();
                    o.UpatedByDate = Convert.ToDateTime(r["UpdatedDate"]);
                    list.Add(o);
                }
            }

            return list;
        }

        public InternalUser GetInternalUserById(int userId)
        {
            InternalUser user = new InternalUser();
            DataSet ds = InternalUserDAL.GetInstance().GetInternalUserById(userId);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];                
                
                user.UserId = Convert.ToInt32(r["UserId"]);
                user.UserName = r["UserName"].ToString().Trim();
                user.UserTypeCode = r["UserTypeCode"].ToString().Trim();                
                user.StatusCode = r["StatusCode"].ToString().Trim();                
                user.CreatedById = Convert.ToInt32(r["CreatedBy"]);                
                user.CreatedByDate = Convert.ToDateTime(r["CreatedDate"]);
                user.UpdatedById = Convert.ToInt32(r["UpdatedBy"]);                
                user.UpatedByDate = Convert.ToDateTime(r["UpdatedDate"]);
            }
            else
                throw new ApplicationException("Data not found.");

            return user;
        }

        public void Delete(int userId)
        {
            InternalUserDAL.GetInstance().Delete(userId);
        }

        public void Add(InternalUser user)
        {
            int count = InternalUserDAL.GetInstance().CountByUserNameInsert(user.UserName);

            if (count == 0)
                InternalUserDAL.GetInstance().Add(user);
            else
                throw new ApplicationException("Duplicate user name.");
        }

        public void Update(InternalUser user)
        {
            int count = InternalUserDAL.GetInstance().CountByUserNameUpdate(user.UserId, user.UserName);

            if (count == 0)
                InternalUserDAL.GetInstance().Update(user);
            else
                throw new ApplicationException("Duplicate user name.");            
        }

        private UserRole GetUserRoleDetail(InternalUser user)
        {
            UserRole role = UserRole.Undefined;

            if (user.UserTypeCode.Equals("A", StringComparison.OrdinalIgnoreCase)) //Administrator
            {
                if (user.StatusCode.Equals("E", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Administrator_Enabled;
                else if (user.StatusCode.Equals("P", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Administrator_Pending;
                else if (user.StatusCode.Equals("D", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Administrator_Disabled;
            }
            else if (user.UserTypeCode.Equals("M", StringComparison.OrdinalIgnoreCase)) //Modulator
            {
                if (user.StatusCode.Equals("E", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Modulator_Enabled;
                else if (user.StatusCode.Equals("P", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Modulator_Pending;
                else if (user.StatusCode.Equals("D", StringComparison.OrdinalIgnoreCase))
                    role = UserRole.Modulator_Disabled;
            }

            return role;
        }

        public UserRole GetUserRole(string strUserName, out InternalUser user)
        {
            UserRole role = UserRole.User;            
            DataSet ds = InternalUserDAL.GetInstance().GetInternalUserByUserName(strUserName);
            user = new InternalUser();

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];

                user.UserId = Convert.ToInt32(r["UserId"]);
                user.UserName = r["UserName"].ToString().Trim();
                user.UserTypeCode = r["UserTypeCode"].ToString().Trim();
                user.StatusCode = r["StatusCode"].ToString().Trim();
                user.CreatedById = Convert.ToInt32(r["CreatedBy"]);
                user.CreatedByDate = Convert.ToDateTime(r["CreatedDate"]);
                user.UpdatedById = Convert.ToInt32(r["UpdatedBy"]);
                user.UpatedByDate = Convert.ToDateTime(r["UpdatedDate"]);
                role = GetUserRoleDetail(user);
            }            

            return role;
        }

        public static InternalUserBLL GetInstance()
        {
            if (_InternalUserBLL == null)
                _InternalUserBLL = new InternalUserBLL();

            return _InternalUserBLL;
        }        

        #endregion
    }
}